﻿using Google.Protobuf.WellKnownTypes;
using MySql.Data.MySqlClient;
using Org.BouncyCastle.Crypto.Engines;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace PizzaSystem
{
    public class SqlDao
    {
        static string url = @"server=localhost;Allow User Variables=True;database=pizza_system; uid=root;pwd=123456;Character Set=utf8;";

        public MySqlConnection getConnection()
        {
            MySqlConnection conn;
            try
            {
                conn = new MySqlConnection(url);
                conn.Open();
                return conn;
            }
            catch (Exception ex)
            {
                return null;
            }
        }

        MySqlDataAdapter adapter;

        MySqlDataReader reader;

        DataSet ds;

        MySqlConnection conn;

        MySqlCommand comm;

        string sql;

        public Custom userLogin(string id, string pass)
        {
            Custom custom = new Custom();
            custom.C_id = -1;
            try
            {
                conn = getConnection();
                sql = "select c_id, c_pass, c_name, c_tel, c_addr, c_account from custom where c_id=@id and c_pass=@pass";
                comm = new MySqlCommand(sql, conn);
                comm.Parameters.AddWithValue("id", id);
                comm.Parameters.AddWithValue("pass", pass);
                reader = comm.ExecuteReader();
                if (reader.Read())
                {
                    custom.C_id = Convert.ToInt32(reader["c_id"].ToString());
                    custom.C_account = Convert.ToDecimal(reader["c_account"].ToString());
                    custom.C_tel = reader["c_tel"].ToString();
                    custom.C_addr = reader["c_addr"].ToString();
                    custom.C_name = reader["c_name"].ToString();
                    custom.C_pass = reader["c_pass"].ToString();
                }
                return custom;
            }
            catch (Exception ex)
            {
                return custom;
            }
            finally
            {
                conn.Close();
            }
        }

        public Vendor vendorLogin(string id, string pass)
        {
            Vendor vendor = new Vendor();
            vendor.V_id = -1;
            try
            {
                conn = getConnection();
                sql = "select v_id, v_pass, v_name, v_tel, v_addr, v_account from vendor where v_id=@id and v_pass=@pass";
                comm = new MySqlCommand(sql, conn);
                comm.Parameters.AddWithValue("id", id);
                comm.Parameters.AddWithValue("pass", pass);
                reader = comm.ExecuteReader();
                if (reader.Read())
                {
                    vendor.V_id = Convert.ToInt32(reader["v_id"].ToString());
                    vendor.V_account = Convert.ToDecimal(reader["v_account"].ToString());
                    vendor.V_tel = reader["v_tel"].ToString();
                    vendor.V_addr = reader["v_addr"].ToString();
                    vendor.V_name = reader["v_name"].ToString();
                    vendor.V_pass = reader["v_pass"].ToString();
                }
                return vendor;
            }
            catch (Exception ex)
            {
                return vendor;
            }
            finally
            {
                conn.Close();
            }
        }

        public bool updateCustom(Custom custom)
        {
            try
            {
                conn = getConnection();
                sql = "update custom set c_name=@name, c_addr=@addr, c_tel=@tel where c_id=@id";
                comm = new MySqlCommand(sql, conn);
                comm.Parameters.AddWithValue("addr", custom.C_addr);
                comm.Parameters.AddWithValue("id", custom.C_id);
                comm.Parameters.AddWithValue("name", custom.C_name);
                comm.Parameters.AddWithValue("tel", custom.C_tel);
                return comm.ExecuteNonQuery() > 0;
            }
            catch (Exception ex)
            {
                return false;
            }
            finally
            {
                conn.Close();
            }
        }

        public Admin adminLogin(string id, string pass)
        {
            Admin admin = new Admin();
            admin.A_id = "";
            try
            {
                conn = getConnection();
                sql = "select a_id, a_pass, a_account from admin where a_id=@id and a_pass=@pass";
                comm = new MySqlCommand(sql, conn);
                comm.Parameters.AddWithValue("id", id);
                comm.Parameters.AddWithValue("pass", pass);
                reader = comm.ExecuteReader();
                if (reader.Read())
                {
                    admin.A_id = reader["a_id"].ToString();
                    admin.A_pass = reader["a_pass"].ToString();
                    admin.A_account = decimal.Parse(reader["a_account"].ToString());
                }
                return admin;
            }
            catch (Exception ex)
            {
                return admin;
            }
            finally
            {
                conn.Close();
            }
        }

        public List<Pizza> selectPizza()
        {
            List<Pizza> list = new List<Pizza>();
            try
            {
                conn = getConnection();
                sql = "select p_id,p_name,p_num,p_price from pizza";
                comm = new MySqlCommand(sql, conn);
                reader = comm.ExecuteReader();
                while (reader.Read())
                {
                    Pizza pizza = new Pizza();
                    pizza.P_id = Int64.Parse(reader["p_id"].ToString());
                    pizza.P_name = reader["p_name"].ToString();
                    pizza.P_price = Convert.ToDecimal(reader["p_price"].ToString());
                    pizza.P_num = Convert.ToInt32(reader["p_num"].ToString());
                    list.Add(pizza);
                }
                return list;
            }
            catch (Exception ex)
            {
                return list;
            }
            finally
            {
                conn.Close();
            }
        }

        public DataSet selectPizzaToDs()
        {
            try
            {
                conn = getConnection();
                sql = "select p_id 披萨编号,p_name 披萨名,p_num 库存,p_price '单价(元)' from pizza";
                comm = new MySqlCommand(sql, conn);
                adapter = new MySqlDataAdapter(comm);
                ds = new DataSet();
                adapter.Fill(ds);
                return ds;
            }
            catch (Exception ex)
            {
                return ds;
            }
            finally
            {
                conn.Close();
            }
        }

        public bool updatePizzaNumById(long p_id, int num, bool isAdd, MySqlConnection con)
        {
            try
            {
                sql = "update pizza set p_num=p_num-@num where p_id=@id";
                if (isAdd)
                {
                    sql = "update pizza set p_num=p_num+@num where p_id=@id";
                }
                comm = new MySqlCommand(sql, con);
                comm.Parameters.AddWithValue("id",p_id);
                comm.Parameters.AddWithValue("num",num);
                int n = comm.ExecuteNonQuery();
                return n>0;
            }
            catch (Exception ex)
            {
                return false;
            }
        }

        private Dictionary<string,string> getInventory(long p_id, int num)
        {
            Dictionary<string, string> keyValues = new Dictionary<string, string>();
            try
            {
                conn = getConnection();
                sql = "SELECT i_id, f_num from formula WHERE p_id=@id";
                comm = new MySqlCommand(sql, conn);
                comm.Parameters.AddWithValue("id", p_id);
                reader = comm.ExecuteReader();
                while (reader.Read())
                {
                    keyValues.Add(reader["i_id"].ToString(), (num * int.Parse(reader["f_num"].ToString())).ToString());
                }
                return keyValues;
            }
            catch { 
                return null; 
            }
            finally
            {
                conn.Close();
            }
           
        }

        public bool updateInventoryNum(long p_id, int num)
        {

            Dictionary<string, string> keyValues = getInventory(p_id, num);
            conn = getConnection();
            MySqlTransaction transaction = conn.BeginTransaction();
            try
            {
                foreach (var item in keyValues)
                {
                    sql = "update inventory set i_num=i_num-@num WHERE i_id=@id";
                    comm = new MySqlCommand(sql, conn);
                    comm.Parameters.AddWithValue("id", item.Key);
                    comm.Parameters.AddWithValue("num", item.Value);
                    if (comm.ExecuteNonQuery()<0)
                    {
                       transaction.Rollback();
                        return false;
                    }
                }
                transaction.Commit();
                return true;
            }
            catch (Exception ex)
            {
                transaction.Rollback();
                return false;
            }
            finally { conn.Close(); }
        }

        public bool updateInventoryNum(long i_id, int num, bool isAdd, MySqlConnection con)
        {
            try
            {
                sql = "update inventory set i_num=i_num-@num WHERE i_id=@id";
                if (isAdd)
                {
                    sql = "update inventory set i_num=i_num+@num WHERE i_id=@id";
                }
                comm = new MySqlCommand(sql, con);
                comm.Parameters.AddWithValue("id", i_id);
                comm.Parameters.AddWithValue("num", num);
                
                return comm.ExecuteNonQuery()>0;
            }
            catch (Exception ex)
            {
                return false;
            }
        }

        public bool insertBill(Bill bill)
        {
            try
            {
                conn = getConnection();
                sql = "insert into bill(p_id,c_id,b_num,b_money,b_time,b_id) " +
                    " values(@p_id,@c_id,@b_num,@b_money,@b_time,@b_id)";
                comm = new MySqlCommand(sql, conn);
                comm.Parameters.AddWithValue("p_id", bill.P_id);
                comm.Parameters.AddWithValue("c_id", bill.C_id);
                comm.Parameters.AddWithValue("b_money", bill.B_money);
                comm.Parameters.AddWithValue("b_num", bill.B_num);
                comm.Parameters.AddWithValue("b_time", bill.B_time);
                comm.Parameters.AddWithValue("b_id", bill.B_id);
                return comm.ExecuteNonQuery()>0;
            }
            catch (Exception ex)
            {
                return false;
            }
            finally
            {
                conn.Close();
            }
        }

        public bool insertScheme(Scheme scheme)
        {
            try
            {
                conn = getConnection();
                sql = "insert into scheme(p_id,s_id,s_num,s_place) " +
                    " values(@p_id,@s_id,@num,@place)";
                comm = new MySqlCommand(sql, conn);
                comm.Parameters.AddWithValue("p_id", scheme.P_id);
                comm.Parameters.AddWithValue("s_id", scheme.S_id);
                comm.Parameters.AddWithValue("num", scheme.S_num);
                comm.Parameters.AddWithValue("place", scheme.S_place);
                return comm.ExecuteNonQuery() > 0;
            }
            catch (Exception ex)
            {
                return false;
            }
            finally
            {
                conn.Close();
            }
        }
        public bool updateBillState(string b_id, long c_id, int state)
        {
            try
            {
                conn = getConnection();
                sql = "update bill set b_state=@state where b_id=@b_id and c_id=@c_id";
                comm = new MySqlCommand(sql, conn);
                comm.Parameters.AddWithValue("state", state);
                comm.Parameters.AddWithValue("c_id", c_id);
                comm.Parameters.AddWithValue("b_id", b_id);
                return comm.ExecuteNonQuery() > 0;
            }
            catch (Exception ex)
            {
                return false;
            }
            finally
            {
                conn.Close();
            }
        }
        public DataSet selectBillById(long c_id,int state)
        {
            try
            {
                conn = getConnection();
                sql = "select b_id 订单号,p_id 披萨编号, p_name 披萨类型,b_num 数量,b_money 金额,b_time 时间" +
                    " from bill join pizza using(p_id) " +
                    " where c_id=@c_id and b_state=@state";
                comm = new MySqlCommand(sql, conn);
                comm.Parameters.AddWithValue("c_id",c_id);
                comm.Parameters.AddWithValue("state",state);
                adapter = new MySqlDataAdapter(comm);
                ds = new DataSet();
                adapter.Fill(ds);
                return ds;
            }
            catch (Exception ex)
            {
                return ds;
            }
            finally
            {
                conn.Close();
            }
        }

        public bool updateBillFinish(Bill bill)
        {
            conn = getConnection();
            MySqlTransaction transaction= null;
            try
            {
               
                transaction = conn.BeginTransaction();
                sql = "update bill set b_state=@state,b_real_time=@time where b_id=@b_id and c_id=@c_id";
                comm = new MySqlCommand(sql, conn);
                comm.Parameters.AddWithValue("state", bill.B_state);
                comm.Parameters.AddWithValue("c_id", bill.C_id);
                comm.Parameters.AddWithValue("b_id", bill.B_id);
                comm.Parameters.AddWithValue("time", bill.B_real_time);
                if (comm.ExecuteNonQuery() > 0 && updatePizzaNumById(bill.P_id, bill.B_num ,false, conn))
                {
                    transaction.Commit();
                    return true;
                }
                transaction.Rollback();
                return false;
            }
            catch (Exception ex)
            {
                transaction.Rollback();
                return false;
            }
            finally
            {
                conn.Close();
            }
        }

        public DataSet selectBill(int state)
        {
            try
            {
                conn = getConnection();
                sql = "select b_id 订单号,c_id 客户ID,c_name 客户名, c_tel 电话, c_addr 地址, p_id 披萨编号, p_name 披萨类型,b_num 数量,b_money 金额,b_time 时间" +
                    " from bill join pizza using(p_id) join custom using(c_id)" +
                    " where b_state=@state";
                comm = new MySqlCommand(sql, conn);
                comm.Parameters.AddWithValue("state", state);
                adapter = new MySqlDataAdapter(comm);
                ds = new DataSet();
                adapter.Fill(ds);
                return ds;
            }
            catch (Exception ex)
            {
                return ds;
            }
            finally
            {
                conn.Close();
            }
        }

        public DataSet selectInventoryBill(long v_id, int state)
        {
            try
            {
                conn = getConnection();
                sql = "SELECT ib_id 订单编号, i_id 原料编号, i_name 原料名,ib_num 数量, ib_money 金额, " +
                    "ib_time 时间 FROM `inventory_bill` JOIN inventory USING(i_id) WHERE inventory_bill.v_id = @id and ib_state=@state";
                comm = new MySqlCommand(sql, conn);
                comm.Parameters.AddWithValue("state", state);
                comm.Parameters.AddWithValue("id", v_id);
                adapter = new MySqlDataAdapter(comm);
                ds = new DataSet();
                adapter.Fill(ds);
                return ds;
            }
            catch (Exception ex)
            {
                return ds;
            }
            finally
            {
                conn.Close();
            }
        }

        public bool updateInventoryBillFinish(string ib_id, long i_id, int i_num)
        {
            conn = getConnection();
            MySqlTransaction transaction = null;
            try
            {

                transaction = conn.BeginTransaction();
                sql = "update inventory_bill set ib_state=@state where ib_id=@ib_id";
                comm = new MySqlCommand(sql, conn);
                comm.Parameters.AddWithValue("state", 1);
                comm.Parameters.AddWithValue("ib_id", ib_id);
                if (comm.ExecuteNonQuery() > 0 && updateInventoryNum(i_id, i_num, true, conn))
                {
                    transaction.Commit();
                    return true;
                }
                transaction.Rollback();
                return false;
            }
            catch (Exception ex)
            {
                transaction.Rollback();
                return false;
            }
            finally
            {
                conn.Close();
            }
        }

        public DataSet selectInventory()
        {
            try
            {
                conn = getConnection();
                sql = "SELECT i_id 材料编号, i_name 材料名, i_num 库存, i_price 价格, v_id 供应商号,v_name 供应商名, " +
                    "v_tel 电话, v_addr 地址 FROM inventory JOIN vendor USING(v_id)";
                comm = new MySqlCommand(sql, conn);
                adapter = new MySqlDataAdapter(comm);
                ds = new DataSet();
                adapter.Fill(ds);
                return ds;
            }
            catch (Exception ex)
            {
                return ds;
            }
            finally
            {
                conn.Close();
            }
        }

        public bool insertInventoryBill(InventoryBill inventory)
        {
            try
            {
                conn = getConnection();
                sql = "insert into inventory_bill(v_id,i_id,ib_num,ib_money,ib_time,ib_id) " +
                    " values(@v_id,@i_id,@num,@money,@time,@id)";
                comm = new MySqlCommand(sql, conn);
                comm.Parameters.AddWithValue("v_id", inventory.V_id);
                comm.Parameters.AddWithValue("i_id", inventory.I_id);
                comm.Parameters.AddWithValue("money", inventory.Ib_money);
                comm.Parameters.AddWithValue("num", inventory.Ib_num);
                comm.Parameters.AddWithValue("time", inventory.Ib_time);
                comm.Parameters.AddWithValue("id", inventory.Ib_id);
                return comm.ExecuteNonQuery() > 0;
            }
            catch (Exception ex)
            {
                return false;
            }
            finally
            {
                conn.Close();
            }
        }

        public DataSet selectScheme()
        {
            try
            {
                conn = getConnection();
                sql = "SELECT s_id 计划编号,p_id 披萨编号,p_name 披萨名,p_num 现有库存," +
                    "s_num 生产数量,s_place 生产顺序 FROM scheme JOIN pizza USING(p_id)";
                comm = new MySqlCommand(sql, conn);
                adapter = new MySqlDataAdapter(comm);
                ds = new DataSet();
                adapter.Fill(ds);
                return ds;
            }
            catch (Exception ex)
            {
                return ds;
            }
            finally
            {
                conn.Close();
            }
        }
    }
}
